"""
Case Type   : GUC
Case Name   : 方式二修改track_function为pl，校验其预期结果
Create At   : 2020/12/28
Owner       : opentestcase018
Description :
    1、查询track_function默认值;查询pg_stat_user_functions视图
    show track_function;
    select count(*) from pg_stat_user_functions;
    2、方式二修改track_function为pl，校验其预期结果
    gs_guc reload -N all -I all -c "track_function=pl"
    show track_function;
    3、查询pg_stat_user_functions视图
    4、调用函数，查询pg_stat_user_functions
    5、清理环境
Expect      :
    1、显示默认值none;pg_stat_user_functions视图为空
    2、参数修改成功，校验修改后参数值为pl
    3、查询pg_stat_user_functions为空
    4、调用函数无报错，查询pg_stat_user_functions不为空
    5、清理环境成功
History     :
    Modified: 2022/05/26  modified by opentestcase013: 增加等待时间、优化格式
"""

import unittest
import os

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

LOGGER = Logger()
COMMONSH = CommonSH("PrimaryDbUser")


class Guctestcase(unittest.TestCase):
    def setUp(self):
        LOGGER.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.func_name = 'func_statistics_0044'
        status = COMMONSH.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)

    def test_guc(self):
        LOGGER.info('--step1.1:查询track_function默认值；expect:默认值none--')
        res = COMMONSH.execut_db_sql("show track_functions;")
        LOGGER.info(res)
        self.assertEqual("none", res.splitlines()[-2].strip())

        LOGGER.info('--step1.2:查询pg_stat_user_functions视图；expect:为空--')
        res = COMMONSH.execut_db_sql("select count(*) "
                                     "from pg_stat_user_functions;")
        LOGGER.info(res)
        self.assertEqual(int(res.splitlines()[-2].strip()), 0)

        LOGGER.info('--step2.1:方式二修改track_function为pl；expect:成功--')
        res = COMMONSH.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                     "track_functions=pl")
        self.assertTrue(res)

        LOGGER.info('--step2.2:重启后，查询track_functions；expect:显示pl--')
        COMMONSH.restart_db_cluster()
        res = COMMONSH.get_db_cluster_status()
        self.assertTrue("Degraded" in res or "Normal" in res)
        res = COMMONSH.execut_db_sql("show track_functions;")
        LOGGER.info(res)
        self.assertEqual("pl", res.splitlines()[-2].strip())

        LOGGER.info('--step3:查询pg_stat_user_functions视图；expect:为空--')
        res = COMMONSH.execut_db_sql("select count(*) "
                                     "from pg_stat_user_functions;")
        LOGGER.info(res)
        self.assertEqual(int(res.splitlines()[-2].strip()), 0)

        LOGGER.info('--step4.1:调用函数；expect:成功--')
        sql_cmd = f'''
        drop function if exists {self.func_name}(c int);
        create or replace function {self.func_name}(c int)return number as
        b int := c;
        begin
            for i in 1..c loop
                b:= b - 1;
            end loop;
            return b;
        end;
        select {self.func_name}(5);
        '''
        res = COMMONSH.execut_db_sql(sql_cmd)
        LOGGER.info(res)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], res)

        LOGGER.info('--step4.2:等待1min,查询pg_stat_user_functions视图；'
                    'expect:不为空--')
        sql_cmd = COMMONSH.execut_db_sql("select pg_sleep(60);select count(*) "
                                         "from pg_stat_user_functions;")
        LOGGER.info(sql_cmd)
        self.assertGreater(int(sql_cmd.splitlines()[-2].strip()), 0)

    def tearDown(self):
        LOGGER.info('--step5:清理环境；expect:成功')
        LOGGER.info('--step5.1:删除function；expect:成功')
        drop_func = COMMONSH.execut_db_sql(f'drop function {self.func_name};')
        LOGGER.info(drop_func)

        LOGGER.info('--step5.2:恢复默认值；expect:成功')
        res = COMMONSH.execut_db_sql("show track_functions;")
        if "none" != res.splitlines()[-2].strip():
            COMMONSH.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                   "track_functions='none'")
            COMMONSH.restart_db_cluster()
        status = COMMONSH.get_db_cluster_status()
        LOGGER.info(status)
        LOGGER.info(f'-----{os.path.basename(__file__)} end-----')
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, drop_func)
        self.assertTrue("Degraded" in status or "Normal" in status)
